package com.googlecode.cswish.struts.hibernate;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.googlecode.cswish.struts.ContextHolder;
import com.googlecode.cswish.struts.spring.SystemEnv;
import com.googlecode.cswish.util.FileUtil;

/**
 * Installation directory:
 * <pre>
 * espirit/
 * 	java/
 * 	jetty/
 * 	mysql/
 * 	espirit.bat
 * </pre>
 * @author Jerry
 *
 */
public class DbTools {

	private static final Log logger = LogFactory.getLog(DbTools.class);
	
	public final static String JDBC_DATABASE_TYPE = "jdbc.database";
	public final static String JDBC_DRIVER_NAME = "jdbc.driverClassName";
	public final static String JDBC_USERNAME = "jdbc.username";
	public final static String JDBC_PASSWORD = "jdbc.password";
	public final static String JDBC_URL = "jdbc.url";
	
	private final static DbTools instance = new DbTools();
	
	private DbTools() {
	}
	
	public static DbTools getInstance() {
		return instance;
	}

	/**
	 * ./mysql --default-character-set=utf8 -P3306 -S/tmp/mysql.sock3306 -uroot -p
	 * ./mysqld --defaults-file=/usr/local/guma/mysql/my.ini --user=mysql --socket=/tmp/mysql.sock3306
	 * 
	 * Check and startup the db instance
	 */
	public void checkAndStartupDb() {
		Properties jdbcProperties = getDbInfo();
		boolean startupMysql =  "MYSQL".equals(jdbcProperties.getProperty(JDBC_DATABASE_TYPE)); // jdbc.database
		
		if (startupMysql) {
			String url = jdbcProperties.getProperty(JDBC_URL);
			String mysqlDir = getMysqlDir();
			
			if (!isMysqlRunning(jdbcProperties)) {
				logger.debug("Startup db instance ...");
				
				int portBegin = url.indexOf(':', url.indexOf("//") + 2) + 1;
				int portEnd = url.indexOf('/', portBegin);
				String port = url.substring(portBegin, portEnd);
				fixMysqlCfg(mysqlDir, port);
				
				boolean isLinux = (File.separatorChar == '/');
				
				final String cmd;
				if (isLinux) {
					cmd = mysqlDir + "bin/mysqld --defaults-file=" + mysqlDir + "my.ini --user=root --socket=/tmp/mysql.sock" + port;
				} else {
					cmd = mysqlDir + "bin/mysqld --console";
				}
				
				try {
					final Process p = runCmd(cmd, false, null, null, null);	
					Runtime.getRuntime().addShutdownHook(new Thread() {
						@Override
						public void run() {
							logger.debug("Close db instance");
							p.destroy();
						}
					});
				} catch (Exception ex) {
					logger.error("Failed startup db", ex);
				}
			} else {
				if (logger.isDebugEnabled()) {
					int index = url.indexOf('?');
					if (index > 0) {
						url = url.substring(0, index);
					}
					logger.debug("DB instance has existed, use it directly: " + url);
				}
			}
			
//			boolean enableInitDb = false;
//			if (enableInitDb) {
//				String username = jdbcProperties.getProperty("jdbc.username");
//				String password = jdbcProperties.getProperty("jdbc.password");
//				checkAndTryInitDb(mysqlDir, url, username, password);
//			}
		}
	}
	
	/*
	private void checkAndTryInitDb(String mysqlDir, String url, String user,
			String password) {
		int pos = url.lastIndexOf("/");
		String db = url.substring(pos + 1);
		boolean isMissDatabase = !dbIsReady(db);

		if (isMissDatabase) {
			boolean isJunitEnv = "true".equals(System.getProperty(SystemEnv.JUNIT_TEST));
			
			if (!isJunitEnv) {
				String dbName = getDbName(url);
				//String backupFilename = mysqlDir + "backup/" + dbName + "-init.sql";
				String backupFilename = mysqlDir + "backup/" + ISite.DEFAULT_NAME + "-init.sql";
				File backupFile = new File(backupFilename);
				if (backupFile.exists()) {
					if (logger.isInfoEnabled()) {
						logger.info("found db connection, but no database, try to init it (file size is "
								+ FileUtils.sizeOf(backupFile) + ")");
					}
					restore(backupFilename, dbName);
				} else {
					logger.error("Skip init db, Can't find the init sql file from " + backupFilename);
				}
			} else {
				logger.info("found db connection, but no database");
			}
		} else {
			logger.info("found db connection");
		}
	}

	private void checkAndTryInitSaasDb(String mysqlDir, String url, String user,
			String password) {
		boolean isMissDatabase = !dbIsReady(ISite.DEFAULT_NAME);

		if (isMissDatabase) {
			boolean isJunitEnv = "true".equals(System.getProperty(SystemEnv.JUNIT_TEST));
			
			if (!isJunitEnv) {
				String dbName = getDbName(url);
				//String backupFilename = mysqlDir + "backup/" + dbName + "-init.sql";
				String backupFilename = mysqlDir + "backup/" + ISite.DEFAULT_NAME + "-init.sql";
				File backupFile = new File(backupFilename);
				if (backupFile.exists()) {
					if (logger.isInfoEnabled()) {
						logger.info("found db connection, but no database, try to init it (file size is "
								+ FileUtils.sizeOf(backupFile) + ")");
					}
					restore(backupFilename, dbName);
				} else {
					logger.error("Skip init db, Can't find the init sql file from " + backupFilename);
				}
			} else {
				logger.info("found db connection, but no database");
			}
		} else {
			logger.info("found db connection");
		}
	}
	*/

	public boolean dbIsReady(String site) {
		Properties jdbcProperties = getDbInfo();
		String url = jdbcProperties.getProperty(JDBC_URL);
		if (site != null) {
			int dbBegin = url.lastIndexOf('/');
			int dbEnd = url.lastIndexOf('?');
			url = url.substring(0, dbBegin + 1) + site + url.substring(dbEnd);
		}
		String username = jdbcProperties.getProperty("jdbc.username");
		String password = jdbcProperties.getProperty("jdbc.password");
		boolean hasDb = hasDatabase(url, username, password, true);
		boolean hasTable = false;
		if (hasDb) {
			Connection conn = null;
			Statement stmt = null;
			try {
				conn = DriverManager.getConnection(url, username, password);
				stmt = conn.createStatement();
				stmt.execute("select * from saas_i18n where 1<>1");
				hasTable = true;
			} catch (Exception ex) {
				hasTable = false;
			} finally {
				try {
					if (stmt != null) {
						stmt.close();
					}
				} catch (Exception ex) {
					logger.error("close statement", ex);
				}
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (Exception ex) {
					logger.error("close connection", ex);
				}
			}
		}
		return hasDb && hasTable;
	}
	
	private boolean hasDatabase(String url, String username, String password, boolean waitConnection) {
		int index = url.indexOf('?');
		if (index > 0) {
			url = url.substring(0, index);
		}
		
		Connection conn = null;
		boolean isMissDatabase = false;
		try {
			if (logger.isDebugEnabled()) {
				logger.debug("begin check db connection: " + url);
			}
			
//			Class.forName("org.postgresql.Driver");
			while (conn == null) {
				try {
					conn = DriverManager.getConnection(url, username, password);
				} catch (Exception ex) {
					String errorMsg = ex.getMessage();
					isMissDatabase = errorMsg.indexOf("Unknown database ") >= 0;
					if (isMissDatabase || !waitConnection) {
						break;
					}
					
					logger.debug("Can't find db connection, wait 1 second and try again");
					try {
						Thread.sleep(1000);
					} catch (Exception ex2) {
						logger.error("wait thread", ex2);
					}
				}
			}
		} catch (Exception ex) {
			logger.error("Failed to load mysql driver", ex);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (Exception ex) {
				logger.error("close connection", ex);
			}
		}
		return !isMissDatabase;
	}

	public Properties getDbInfo() {
		String jdbcName = "/jdbc.properties";
		Properties jdbcProperties = new Properties();
		InputStream input = null;
		try {
			input = SystemEnv.class.getResourceAsStream(jdbcName);
			jdbcProperties.load(input);
		} catch (IOException ex) {
			logger.error(
					"Failed to load the jdbc configuration: " + jdbcName, ex);
		} finally {
			IOUtils.closeQuietly(input);
		}
		return jdbcProperties;
	}
	
	private void fixMysqlCfg(String mysqlDir, String port) {
		String runModel = System.getProperty(SystemEnv.RUNNING_MODEL);
		String iniFileName;
		if (SystemEnv.RUNNING_MODEL_SMALL.equals(runModel)) {
			iniFileName = "my-espirit-small.ini";
		} else if (SystemEnv.RUNNING_MODEL_HUGE.equals(runModel)) {
			iniFileName = "my-espirit-huge.ini";
		} else {
			iniFileName = "my-espirit.ini";
		}
		
		File mysqlCfgTplFile = new File(mysqlDir + iniFileName);
		try {
			String cfg = FileUtils.readFileToString(mysqlCfgTplFile);
			File mysqlCfgFile = new File(mysqlDir + "my.ini");
			
			int blockBegin = cfg.indexOf("\n[mysqld]");
			int portBeginIndex = cfg.indexOf("\nport=", blockBegin);
			int portEndIndex = cfg.indexOf("\n", portBeginIndex + 1);
			int baseDirBeginIndex = cfg.indexOf("\nbasedir=", portEndIndex);
			int baseDirEndIndex = cfg.indexOf("\n", baseDirBeginIndex + 1);
			int dataDirBeginIndex = cfg.indexOf("\ndatadir=", baseDirEndIndex);
			int dataDirEndIndex = cfg.indexOf("\n", dataDirBeginIndex + 1);
			
			if (portBeginIndex == -1 || baseDirBeginIndex == -1 || dataDirBeginIndex == -1) {
				logger.error("Wrong format: my.ini!");
			} else {
				String newCfg = cfg.substring(0, portBeginIndex)
						+ "\nport=" + port + cfg.substring(portEndIndex, baseDirBeginIndex)
						+ "\nbasedir=\"" + mysqlDir + "\"" + cfg.substring(baseDirEndIndex, dataDirBeginIndex)
						+ "\ndatadir=\"" + mysqlDir + "data/\"" + cfg.substring(dataDirEndIndex);
				if (!newCfg.equals(cfg)) {
					// update the configuration
					FileUtils.writeStringToFile(mysqlCfgFile, newCfg);
				}
			}
		} catch (Exception ex) {
			logger.error("Process cfg file", ex);
		}
		
		// switch mysql configuration
		// check ib_logfile0 & ib_logfile1
		/*
		String strLogFile0 = mysqlDir + "data/ib_logfile0";
		String strLogFile1 = mysqlDir + "data/ib_logfile1";
		File logFile0 = new File(strLogFile0);
		if (logFile0.exists()) {
			if (useSmallCfg && logFile0.length() > 2048 * 1024 * 10					// small cfg && > 20M
					|| !useSmallCfg && logFile0.length() < 2048 * 1024 * 10) { 		// big cfg && < 20M
				// remove the logfile
				long t = System.currentTimeMillis();
				logFile0.renameTo(new File(strLogFile0 + "." + t));
				new File(strLogFile1).renameTo(new File(strLogFile0 + "." + t));
			}
		}*/
		
		if (logger.isInfoEnabled()) {
			logger.info("Start Mysql(" + port + ") in model: " + runModel + "(1:SMALL, 2:NORMAL, 3:HUGE)");
		}
	}
	
	private boolean isMysqlRunning(Properties jdbcProperties) {
		boolean running = true;
		Connection connection = null;
		try {
			String driverClassName = jdbcProperties.getProperty(JDBC_DRIVER_NAME);
			String url = jdbcProperties.getProperty(JDBC_URL);
			int index = url.indexOf('/', "jdbc:mysql://".length() + 1);
			if (index > 0) {
				url = url.substring(0, index);
			}
			String username = jdbcProperties.getProperty(JDBC_USERNAME);
			String password = jdbcProperties.getProperty(JDBC_PASSWORD);
			Class.forName(driverClassName);
			connection = DriverManager.getConnection(url, username, password);
			// Succes!
		} catch (ClassNotFoundException e) {
			logger.debug("Can't load the driver class");
			running = false;
		} catch (SQLException e) {
			logger.debug("Can't find the mysql connection");
			running = false;
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					logger.error("Failed to close mysql connection", e);
				}
			}
		}
		return running;
	}
	
	private String getMysqlDir() {
		String mysqlDir = System.getProperty("DB_HOME");
		return mysqlDir;
	}
	
	/**
	 * 备份策略可以这么定：第一次全量备份，每天一次增量备份，每周再做一次全量备份，如此一直重复。
	 * 而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。
	 * 为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，
	 * 在 slave 机器上做备份
	 * 
	 * mysqldump --add-drop-database -uroot -p123456 --databases espiritbroker --lock-tables --result-file=d:\init.sql
	 */
	public boolean backup(String backupFilename, String dbname) {
		return backup(backupFilename, dbname, null, false);
	}
	
	public boolean backupTableStructure(String backupFilename, String dbname, String[] tables) {
		return backup(backupFilename, dbname, tables, true);
	}
	
	private boolean backup(String backupFilename, String dbname, String[] tables, boolean onlyTableStructure) {
		File dir = new File(backupFilename).getParentFile();
		dir.mkdirs();
		
		String mysqlDir = getMysqlDir();
		Properties jdbcProperties = getDbInfo();
		String username = jdbcProperties.getProperty(JDBC_USERNAME);
		String password = jdbcProperties.getProperty(JDBC_PASSWORD);
		if (dbname == null) {
			dbname = ContextHolder.get().getSiteName();		// default is current site
		}
		String host = getDbHost(jdbcProperties.getProperty(JDBC_URL));
		String port = getDbPort(jdbcProperties.getProperty(JDBC_URL));
		String tableStructureStr = onlyTableStructure ? "-d" : "";
		String tableStr;
		if (tables == null || tables.length == 0) {
			tableStr = "--databases " + dbname;
		} else {
			tableStr = dbname + ' ' + StringUtils.join(tables, " ");
		}
		
		boolean isLinux = (File.separatorChar == '/');
		String cmd;
		if (isLinux) {
			cmd = String.format("%sbin/mysqldump %s -h%s -P%s -u%s -p%s --lock-tables --result-file=%s --socket=/tmp/mysql.sock%s %s",
							mysqlDir, tableStructureStr, host, port, username, password, backupFilename, port, tableStr);
		} else {
			cmd = String.format("%sbin/mysqldump %s -h%s -P%s -u%s -p%s --lock-tables --result-file=%s %s",
							mysqlDir, tableStructureStr, host, port, username, password, backupFilename, tableStr);
		}
		
//	    cmd = String.format("/usr/local/Cellar/mysql/5.6.26/bin/mysqldump  -u%s -p%s --lock-tables --result-file=%s %s",
//				username, password , backupFilename, tableStr);
		
		boolean ret = runCmd(cmd, true, null, null, null) != null;
		
		if (ret) {
			// remove USE `xxx`;
			String createDB = "CREATE DATABASE IF NOT EXISTS " + dbname
					+ " DEFAULT CHARACTER SET utf8;";
			String useStatement = "USE `" + dbname + "`;";
			FileUtil.replace(new File(backupFilename), new String[] {createDB, useStatement}, new String[] {"", ""});
		} else {
			new File(backupFilename).delete();
		}
		
		return ret;
	}
	
	// mysql -uroot -p123456 < d:\init.sql
	// filename example: db_name.sql
	public void restore(String backupFilename, String dbname) {
		String mysqlDir = getMysqlDir();
		Properties jdbcProperties = getDbInfo();
		String username = jdbcProperties.getProperty(JDBC_USERNAME);
		String password = jdbcProperties.getProperty(JDBC_PASSWORD);
		if (dbname == null || dbname.length() == 0) {
			dbname = getDbName(jdbcProperties.getProperty(JDBC_URL));
		}
		String host = getDbHost(jdbcProperties.getProperty(JDBC_URL));
		String port = getDbPort(jdbcProperties.getProperty(JDBC_URL));
		String socket = "/tmp/mysql.sock" + port;		// TODO: windows need this?
		String cmd = String.format("%sbin/mysql --default-character-set=utf8 -h%s -P%s -S%s -u%s -p%s",
				mysqlDir, host, port, socket, username, password);

//		String cmd = String.format("/usr/local/Cellar/mysql/5.6.26/bin/mysql --default-character-set=utf8 -u%s -p%s",
//				username, password);
		
		
		String exInput = 
				"CREATE DATABASE /*!32312 IF NOT EXISTS*/ `" + dbname + "` /*!40100 DEFAULT CHARACTER SET utf8 */;" + getEnter()
				+ "use `" + dbname + "`;";
		runCmd(cmd, true, null, exInput, backupFilename);
	}
	
	
	public boolean dropDatabase(String projectName, String database) {
		Connection connection = null;
		Statement statement = null;
		try {
			Properties jdbcProperties = getDbInfo();
			String url = jdbcProperties.getProperty(JDBC_URL);
			String username = jdbcProperties.getProperty(JDBC_USERNAME);
			String password = jdbcProperties.getProperty(JDBC_PASSWORD);
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(url, username, password);
			statement = connection.createStatement();
			int result = statement.executeUpdate("DROP DATABASE IF EXISTS " + database);
			return result >= 0;
		} catch (Exception ex) {
			logger.error("drop database", ex);
			return false;
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (Exception ex) {
				logger.error("close statement", ex);
			}
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception ex) {
				logger.error("close connection", ex);
			}
		}
	}
	
	public String getDbName(String url) {
		/* jdbc:mysql://localhost:3307/geniuebroker */
		int driverIndexEnd = url.indexOf('/');
		int ipIndexEnd = url.indexOf('/', driverIndexEnd + 2);
		int dbIndexEnd = url.indexOf('?', ipIndexEnd + 2);
		if (dbIndexEnd == -1) {
			dbIndexEnd = url.length();
		}
		return url.substring(ipIndexEnd + 1, dbIndexEnd);
	}
	
	private String getDbHost(String url) {
		int begin = url.indexOf("//") + 2;
		int end = url.indexOf('/', begin);
		
		String host = url.substring(begin, end);
		int portIndex = host.indexOf(':');
		if (portIndex > 0) {
			host = host.substring(0, portIndex);
		}
		return host;
	}
	
	private String getDbPort(String url) {
		int commaIndex1 = url.indexOf(':');
		int commaIndex2 = url.indexOf(':', commaIndex1 + 1);
		int commaIndex3 = url.indexOf(':', commaIndex2 + 1);
		String port;
		if (commaIndex3 == -1) {
			port = "3306";
		} else {
			int portEnd = url.indexOf('/', commaIndex3 + 1);
			port = url.substring(commaIndex3 + 1, portEnd);
		}
		return port;
	}
	
	private String getEnter() {
		boolean isLinux = (File.separatorChar == '/');
		return isLinux ? "\n" : "\r\n";
	}
	
	private Process runCmd(String cmd, boolean waitResult, String outputFile, String exInputCmd, String inputFile) {
		String cmdTrace = null;
		int cmdNo = 0;
		Process process = null;
		try {
			if (logger.isDebugEnabled()) {
				logger.debug(getCmdInfo("Begin run command: ", cmd, outputFile, inputFile));
			}
			
			process = Runtime.getRuntime().exec(cmd);
			
			if (outputFile != null) {
				// 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行     
	            InputStream in = process.getInputStream();// 控制台的输出信息作为输入流     
	                            
	            InputStreamReader xx = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8，否则从流中读入的是乱码     
	            // 要用来做导入用的sql目标文件：     
	            FileOutputStream fout = new FileOutputStream(outputFile);
	            OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");     
				String enter = getEnter();
				
	            String inStr;     
	            // 组合控制台输出信息字符串     
	            BufferedReader br = new BufferedReader(xx);
	            while ((inStr = br.readLine()) != null) {
	            	cmdTrace = inStr;
	                writer.write(inStr);
	                writer.write(enter);
	            }
	           
	            // 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免     
	            writer.flush();
	    
	            // 别忘记关闭输入输出流     
	            in.close();     
	            xx.close();     
	            br.close();     
	            writer.close();     
	            fout.close(); 
			}
			
			
			if (exInputCmd != null || inputFile != null) {
				OutputStream out = process.getOutputStream();//控制台的输入信息作为输出流     
				OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");
				boolean isLinux = (File.separatorChar == '/');
				String enter = isLinux ? "\n" : "\r\n";
				
				if (exInputCmd != null) {
					cmdTrace = exInputCmd;
					writer.write(exInputCmd);
					writer.write(enter);
				}
				
				if (inputFile != null) {
					String inStr;
					BufferedReader br = new BufferedReader(new InputStreamReader(
							   new FileInputStream(inputFile), "utf8"));
					while ((inStr = br.readLine()) != null) {
						cmdTrace = inStr;
						cmdNo++;
						writer.write(inStr);
						writer.write(enter);
					}
					br.close();     
				}
				 
				// 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免     
				writer.flush();     
				// 别忘记关闭输入输出流     
				out.close();     
				writer.close();
			}
			
			if (waitResult) {
				String str = loadStream(process.getInputStream());
				if (str != null && str.length() > 0) {
					logger.debug(str);
				}
				String err = loadStream(process.getErrorStream());
				if (err != null && err.length() > 0) {
					logger.error(err);
				}
			
				int processComplete = process.waitFor();
				if (processComplete == 0) {
					if (logger.isDebugEnabled()) {
						logger.debug(getCmdInfo("Run command successfully: ", cmd, outputFile, inputFile));
					}
	                return process;
	            } else {
	            	if (inputFile != null) {
	            		logger.debug("Wrong input file parameter?");
	            	}
	                logger.error(getCmdInfo("Run command failed: ", cmd, outputFile, inputFile));
	            }
			} else {
				if (logger.isDebugEnabled()) {
					logger.debug(getCmdInfo("Run command successfully: ", cmd, outputFile, inputFile));
				}
				return process;
			}
		} catch (Exception ex) {
			try {
				String err = loadStream(process.getErrorStream());
				if (err != null && err.length() > 0) {
					logger.error(err);
				}
			} catch (Exception ex2) {
				logger.error("Load error message", ex2);
			}
			
			logger.error("Line=" + cmdNo + ", Current command is: " + cmdTrace);
			logger.error(getCmdInfo("Failed to run cmd: ", cmd, outputFile, inputFile), ex);
		}
		return null;
	}
	
	private String getCmdInfo(String prefix, String cmd, String outputFile, String inputFile) {
		return prefix +  cmd
				+ (outputFile == null ? "" : " > " + outputFile)
				+ (inputFile == null ? "" : " < " + inputFile);
	}
	
	private static String loadStream(InputStream in) throws IOException {
		int ptr = 0;
		in = new BufferedInputStream(in);
		StringBuffer buffer = new StringBuffer();
		while ((ptr = in.read()) != -1) {
			buffer.append((char) ptr);
		}
		return buffer.toString();
	}
}